﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Models;
using System.Data.SQLite;
using Server.Common;
using System.Data;

namespace Server.DAL
{
    public static class CustomSrv
    {
        #region 转换

        private static CustomCateModel DrToCate(DataRow dr)
        {
            if (dr is null)
                return null;
            CustomCateModel cm = new CustomCateModel
            {
                CateCode = Comm.Cdr<string>(dr, "cate_code"),
                CateName = Comm.Cdr<string>(dr, "cate_name"),
                Id = Comm.Cdr<int>(dr, "id")
            };
            return cm;
        }

        private static CustomModel DrToCust(DataRow dr)
        {
            if (null == dr)
                return null;
            CustomModel cm = new CustomModel
            {
                CateId = Comm.Cdr<int>(dr, "cate_id"),
                CateName = Comm.Cdr<string>(dr, "cate_name"),
                ChargeAmount = Comm.Cdr<decimal>(dr, "c_charge_amount"),
                ChargeCount = Comm.Cdr<decimal>(dr, "c_charge_count"),
                CustCode = Comm.Cdr<string>(dr, "c_code"),
                CustName = Comm.Cdr<string>(dr, "c_name"),
                CustPhone = Comm.Cdr<string>(dr, "c_phone"),
                CustState = Comm.Cdr<byte>(dr, "c_state"),
                CustPoints = Comm.Cdr<int>(dr, "c_points"),
                CustTotalPoints = Comm.Cdr<int>(dr, "c_total_points"),
                Id = Comm.Cdr<int>(dr, "id"),
                Remark = Comm.Cdr<string>(dr, "c_remark")
            };
            return cm;
        }

        #endregion 转换

        #region 类别

        #region 增加类别

        public static void AddCate(CustomCateModel cm)
        {
            string sqlstr = $"insert into t_cust_cate(cate_code,cate_name) values('{cm.CateCode}','{cm.CateName}')";
            Comm.DB.Exec(sqlstr);
        }

        #endregion 增加类别

        #region 修改类别

        public static void EditCate(CustomCateModel cm)
        {
            string sqlstr = $"update t_cust_cate set cate_code='{cm.CateCode}',cate_name='{cm.CateName}' where id={cm.Id}";
            Comm.DB.Exec(sqlstr);
        }

        #endregion 修改类别

        #region 删除类别

        public static void DelCate(int cateId)
        {
            string sqlstr = "delete from t_cust_cate where id=" + cateId;
            Comm.DB.Exec(sqlstr);
        }

        #endregion 删除类别

        #region 读取类别信息

        public static CustomCateModel GetCateInfo(int cateId)
        {
            string sqlstr = $"select * from t_cust_cate where id={cateId}";
            var dt = Comm.DB.Query(sqlstr);
            if (dt.Rows.Count < 1)
                return null;
            return DrToCate(dt.Rows[0]);
        }

        public static CustomCateModel GetCateInfo(string cateCode)
        {
            string sqlstr = $"select * from t_cust_cate where cate_code={cateCode}";
            var dt = Comm.DB.Query(sqlstr);
            if (dt.Rows.Count < 1)
                return null;
            return DrToCate(dt.Rows[0]);
        }

        #endregion 读取类别信息

        #region 读类别列表

        public static List<CustomCateModel> GetCateList()
        {
            string sqlstr = "select * from t_cust_cate";
            var dt = Comm.DB.Query(sqlstr);
            if (dt.Rows.Count < 1)
                return null;
            List<CustomCateModel> l = new List<CustomCateModel>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var dr = dt.Rows[i];
                l.Add(DrToCate(dr));
            }
            return l;
        }

        #endregion 读类别列表

        #region 获取类别下的客户数量

        public static int GetCateChildCount(int cateId)
        {
            if (cateId <= 0)
                return 0;
            string sqlstr = "select count(*) from t_custom where cate_id=" + cateId;
            var dt = Comm.DB.Query(sqlstr);
            if (dt.Rows.Count < 1)
                throw new Exception("未知错误");
            return Convert.ToInt32(dt.Rows[0][0]);
        }

        #endregion 获取类别下的客户数量

        #endregion 类别

        #region 客户

        #region 增加客户

        public static void AddCust(CustomModel cm)
        {
            string sqlstr = "insert into t_custom(cate_id,c_code,c_name,c_phone,c_remark) values(@cate_id,@c_code,@c_name,@c_phone,@c_remark)";
            SQLiteParameter[] op = new SQLiteParameter[]
            {
                new SQLiteParameter {DbType=DbType.Int32,ParameterName="@cate_id",Value=cm.CateId},
                new SQLiteParameter {DbType=DbType.String,ParameterName="@c_code",Value=cm.CustCode},
                new SQLiteParameter {DbType=DbType.String,ParameterName="@c_name",Value=cm.CustName },
                new SQLiteParameter {DbType=DbType.String,ParameterName="@c_phone",Value=cm.CustPhone},
                new SQLiteParameter {DbType=DbType.String,ParameterName="@c_remark",Value=cm.Remark}
            };
            Comm.DB.Exec(sqlstr, op);
        }

        #endregion 增加客户

        #region 修改客户

        public static void EditCust(CustomModel cm)
        {
            string sqlstr = "update t_custom set cate_id=@cate_id,c_code=@c_code,c_name=@c_name,c_phone=@c_phone,c_remark=@c_remark where id=@id";
            SQLiteParameter[] op = new SQLiteParameter[]
            {
                new SQLiteParameter {DbType=DbType.Int32,ParameterName="@id",Value=cm.Id},
                new SQLiteParameter {DbType=DbType.Int32,ParameterName="@cate_id",Value=cm.CateId},
                new SQLiteParameter {DbType=DbType.String,ParameterName="@c_code",Value=cm.CustCode },
                new SQLiteParameter {DbType=DbType.String,ParameterName="@c_name",Value=cm.CustName},
                new SQLiteParameter {DbType=DbType.String,ParameterName="@c_phone",Value=cm.CustPhone},
                new SQLiteParameter{DbType=DbType.String,ParameterName="@c_remark",Value=cm.Remark}
            };
            Comm.DB.Exec(sqlstr, op);
        }

        #endregion 修改客户

        #region 启用客户

        public static void EnableCust(int custId)
        {
            string sqlstr = "update t_custom set c_state=0 where id=" + custId;
            Comm.DB.Exec(sqlstr);
        }

        #endregion 启用客户

        #region 禁用客户

        public static void DisableCust(int custId)
        {
            string sqlstr = "update t_custom set c_state=1 where id=" + custId;
            Comm.DB.Exec(sqlstr);
        }

        #endregion 禁用客户

        #region 读客户信息

        public static CustomModel GetCustInfo(int custId)
        {
            string sqlstr = "select t.*,t1.cate_name from t_custom t left join t_cust_cate t1 on t.cate_id = t1.id where t.id=" + custId;
            var dt = Comm.DB.Query(sqlstr);
            if (dt.Rows.Count < 1)
                return null;
            return DrToCust(dt.Rows[0]);
        }

        public static CustomModel GetCustInfo(string custCode)
        {
            string sqlstr = $"select t.*,t1.cate_name from t_custom t left join t_cust_cate t1 on t.cate_id = t1.id where t.c_code='{custCode}'";
            var dt = Comm.DB.Query(sqlstr);
            if (dt.Rows.Count < 1)
                return null;
            return DrToCust(dt.Rows[0]);
        }

        #endregion 读客户信息

        #region 更新用户兑换汇总

        public static void UpdateCustSummary(int custId)
        {
            string sqlstr = $"update t_custom set c_charge_count=(select count(*) from t_code_status where charge_custom=t_custom.id),c_charge_amount = (select ifnull(sum(ifnull(charge_amount,0)),0) as charge_amount from t_code_status where charge_custom=t_custom.id),c_total_points=(select ifnull(sum(ifnull(charge_point,0)),0) as charge_point from t_code_status where charge_custom=t_custom.id) where id={custId};update t_custom set c_points=(select ifnull(sum(ifnull(use_point,0)),0) from t_pointbill where cust_id=t_custom.id) where id={custId}";

            Comm.DB.Exec(sqlstr);
        }

        #endregion 更新用户兑换汇总

        #region 读客户列表

        public static List<CustomModel> GetCustList(int cateId = 0, byte state = 0)
        {
            string sqlstr = $"select t.*,t1.cate_name from t_custom t left join t_cust_cate t1 on t.cate_id=t1.id where 1=1";
            if (state == 1)
                sqlstr += " and t.c_state=1";
            if (state == 0)
                sqlstr += " and t.c_state=0";
            if (cateId > 0)
                sqlstr += $" and t.cate_id={cateId}";
            var dt = Comm.DB.Query(sqlstr);
            if (dt.Rows.Count < 1)
                return null;
            List<CustomModel> l = new List<CustomModel>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var dr = dt.Rows[i];
                l.Add(DrToCust(dr));
            }
            return l;
        }

        #endregion 读客户列表

        #endregion 客户
    }
}